/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Dblayer;
import Modellayer.*;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author bruhn
 */
public class DBVareLinje implements IFDBVareLinje {
    
    private Connection con;
    public DBVareLinje()
    {
	con = DBConnection.getInstance().getDBcon();
    }

    public ArrayList<VareLinje> hentVareLinje(boolean retriveAssociation) {
        return miscWhere("", retriveAssociation);
    }

    public ArrayList<VareLinje> findBestillingsnr(int bestillingsNr, boolean retriveAssociation) {
        return miscWhere("  bestillingsNr = '" + bestillingsNr + "'", retriveAssociation);
    }
    
    //public Bestilling findBestillingsnr(int bestillingsNr, boolean retriveAssociation) {
    //    String wClause = "  BestillingsNr = '" + bestillingsNr + "'";
    //    return singleWhere(wClause, retriveAssociation);
   // } gemt i tilfælde af problemer 

    public int opretVareLinje(VareLinje varelinje) {
    int rc = -1;
    String query="INSERT INTO semVareLinje(vareID, antal, bestillingsNr)  VALUES('"+
	  		varelinje.getVareID().getVareID()  + "','"  +
			varelinje.getAntal() + "','" +
			varelinje.getBestillingsNr().getBestillingsNr() + "')";

      System.out.println("insert : " + query);
      try{ // insert new customer
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in semVareLinje db: "+ex);
	   }
	   return(rc);
	}

	public void slet(int bestillingsNr)
	{
               int rc=-1;

	  	String query="DELETE FROM semVareLinje WHERE bestillingsNr = '" +
				bestillingsNr + "'";
                System.out.println(query);
	  	try{ // delete from customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in semVareLinje db: "+ex);
   	        }
    }

    public void sletBestilling(int vareID, int bestID)
    {
         int rc=-1;

        String query="DELETE FROM semVareLinje WHERE vareID + bestillingsNr = " +
                        vareID + " + " + bestID;
        System.out.println(query);
        try{ // delete from customer
                Statement stmt = con.createStatement();
                stmt.setQueryTimeout(5);
                rc = stmt.executeUpdate(query);
                stmt.close();
        }//slut try
        catch(Exception ex){
                System.out.println("Delete exception in semVareLinje db: "+ex);
        }
    }

     private ArrayList<VareLinje> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<VareLinje> list = new ArrayList<VareLinje>();

	    String query =  buildQuery(wClause);

            try{ // read the persiCustomer from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);

		int snr=0;
		while( results.next() ){
	     	 VareLinje vareLinjeObj = new VareLinje();
		 vareLinjeObj = buildVareLinje(results);
                 list.add(vareLinjeObj);
		}//end while
                 stmt.close();
                 if(retrieveAssociation)
                 {   //The city is to be build as well
                     for(VareLinje vareLinjeObj : list){
						IFDBBestilling dbBestillingsObj = new DBBestilling();
                        Bestilling bestillingsObj = dbBestillingsObj.findBestillingsNr(vareLinjeObj.getBestillingsNr().getBestillingsNr(), false);
                        System.out.println("Bestilling is seleceted ");
						vareLinjeObj.setBestillingsNr(bestillingsObj);
                     }
					 for(VareLinje vareLinjeObj : list){
						IFDBVare dbVareObj = new DBVare();
                        Vare vareObj = dbVareObj.findVare(vareLinjeObj.getVareID().getVareID(), false);
                        System.out.println("vare is seleceted ");
                        vareLinjeObj.setVareID(vareObj);
                     }

                 }//end if

		}//slut try
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

    //Singelwhere is used when we only select one vare
    private VareLinje singleWhere(String wClause, boolean retrieveAssociation)
    {
           ResultSet results;
           VareLinje vareLinjeObj = new VareLinje();

	   String query =  buildQuery(wClause);
           System.out.println(query);
		try{ // read the persiVareLinje from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
                            vareLinjeObj = buildVareLinje(results);
                            //assocaition is to be build
                            stmt.close();
                            if(retrieveAssociation)
                            {
                                IFDBVare dbVareObj = new DBVare();
                                Vare vareObj = dbVareObj.findVare(vareLinjeObj.getVareID().getVareID(), false);
                                System.out.println("vare is seleceted ");
                                vareLinjeObj.setVareID(vareObj);

								IFDBBestilling dbBestillingsObj = new DBBestilling();
								Bestilling bestillingsObj = dbBestillingsObj.findBestillingsNr(vareLinjeObj.getBestillingsNr().getBestillingsNr(), false);
								System.out.println("Bestilling is seleceted ");
								vareLinjeObj.setBestillingsNr(bestillingsObj);
                            }
			}
                        else{ //no vare was found
                            vareLinjeObj = null;
                        }
		}//end try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return vareLinjeObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT vareID, antal, bestillingsNr FROM semVareLinje";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

	private VareLinje buildVareLinje(ResultSet results)
        {
            VareLinje vareLinjeObj = new VareLinje();
            IFDBVare dbVareObj = new DBVare();
            IFDBBestilling dbBestillingsObj = new DBBestilling();
            try{ // the columns from the table persiCustomer are used
                vareLinjeObj.setAntal(results.getInt("antal"));
                vareLinjeObj.setVareID(dbVareObj.findVare(results.getInt("vareID"), false));
                vareLinjeObj.setBestillingsNr(dbBestillingsObj.findBestillingsNr(results.getInt("bestillingsNr"), false));
          }
         catch(Exception e)
         {
             System.out.println("error in building the Vare object");
         }
         return vareLinjeObj;
      }
}
